package qimao.com.edu.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import qimao.com.edu.dao.AuthorDao;
import qimao.com.edu.dao.BaseDao;
import qimao.com.edu.entity.Author;

public class AuthorDaoImpl implements AuthorDao {
	

	@Override
	public Author selectWriterByName(Author author) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int result = 0;
		Author a = null;
		String sql = "SELECT * FROM author WHERE username = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
	        ps.setString(1, author.getUsername());
			rs = ps.executeQuery();
			if(rs.next()) {
				a = new Author();
				a.setAuthorId(rs.getInt("author_id"));
				a.setUsername(rs.getString("username"));
			    a.setPassword(rs.getString("password"));
			    a.setPenName(rs.getString("pen_name"));
			    a.setRealName(rs.getString("real_name"));
			    a.setGender(rs.getString("gender"));
			    a.setAvatar(rs.getString("avatar"));
			    a.setBirthPlace(rs.getString("birth_place"));
			    a.setBio(rs.getString("bio"));
			    a.setWorksCount(rs.getInt("works_count"));
			    a.setStatus(rs.getInt("status"));
			    //生日birthday
			    Timestamp birthday = rs.getTimestamp("birthday");
			    if(birthday!=null) {
			    	a.setBirthday(birthday.toLocalDateTime());
			    }
			    
			    //注册时间register_time
			    Timestamp register_time = rs.getTimestamp("register_time");
			    if(birthday!=null) {
			    	a.setBirthday(register_time.toLocalDateTime());
			    }
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return a;
	}

	@Override
	public Author selectWriterByPenName(Author author) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Author a = null;
		int result = 0;
		String sql = "SELECT * FROM author WHERE pen_name = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
	        ps.setString(1, author.getPenName());
			rs = ps.executeQuery();
			if(rs.next()) {
				a.setAuthorId(rs.getInt("authorId"));
				a.setUsername(rs.getString("username"));
			    a.setPassword(rs.getString("password"));
			    a.setPenName(rs.getString("pen_name"));
			    a.setRealName(rs.getString("real_name"));
			    a.setGender(rs.getString("gender"));
			    a.setAvatar(rs.getString("avatar"));
			    a.setBirthPlace(rs.getString("birth_place"));
			    a.setBio(rs.getString("bio"));
			    a.setWorksCount(rs.getInt("works_count"));
			    a.setStatus(rs.getInt("status"));
			    //生日birthday
			    Timestamp birthday = rs.getTimestamp("birthday");
			    a.setBirthday(birthday.toLocalDateTime());
			    //注册时间register_time
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return a;
	}
	
	@Override
	public int insWriter(Author author) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		 String sql = "INSERT INTO author(username, password, pen_name, gender, avatar, bio,register_time) "
		            + "VALUES (?, ?, ?, ?, ?, ?,now())";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, author.getUsername());
			ps.setString(2, author.getPassword());
			ps.setString(3, author.getPenName());
	        ps.setString(4, author.getGender());
	        ps.setString(5, author.getAvatar());
	        ps.setString(6, author.getBio());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}
	
	@Override
	public Author selectAuthor(Author author) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Author a = null;
		String sql = "SELECT * FROM author WHERE username = ? and password = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, author.getUsername());
			ps.setString(2, author.getPassword());
			System.out.println(ps);
			res = ps.executeQuery();
			if(res.next()) {
				a = new Author();
				a.setAuthorId(res.getInt("author_id"));
				a.setUsername(res.getString("username"));
				a.setPassword(res.getString("password"));
				a.setRoleName(res.getString("roleName"));
				a.setPenName(res.getString("pen_name"));
				a.setRealName(res.getString("real_name"));
				a.setGender(res.getString("gender"));
				a.setAvatar(res.getString("avatar"));
//				author.setBirthday(res.getDate("birthday"));
				a.setBirthPlace(res.getString("birth_place"));
				a.setBio(res.getString("bio"));
				a.setWorksCount(res.getInt("works_count"));
				a.setStatus(res.getInt("status"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return a;
	}
	
	@Override
	public int deleteAuthorById(Integer author_id) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM author WHERE author_id = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,author_id);
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		
		return result;
	}

	@Override
	public Author AuthorByAuthorId(Integer author_id) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Author author = null;
		String sql = "SELECT * FROM author WHERE author_id = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,author_id);
			res = ps.executeQuery();
			if(res.next()) {
				author = new Author();
				author.setAuthorId(res.getInt("author_id"));
				author.setUsername(res.getString("username"));
				author.setPassword(res.getString("password"));
				author.setRoleName(res.getString("rolename"));
				author.setPenName(res.getString("pen_name"));
				author.setRealName(res.getString("real_name"));
				author.setGender(res.getString("gender"));
				author.setAvatar(res.getString("avatar"));
				author.setBirthPlace(res.getString("birth_place"));
				author.setBio(res.getString("bio"));
				author.setWorksCount(res.getInt("works_count"));
				author.setStatus(res.getInt("status"));
//			    //生日birthday
//			    Timestamp birthday = res.getTimestamp("birthday");
//			    author.setBirthday(birthday.toLocalDateTime());
//			    //注册时间register_time
//			    Timestamp register_time = res.getTimestamp("register_time");
//			    author.setBirthday(register_time.toLocalDateTime());
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		return author;
	}

	@Override
	public int updateWorksCount(Integer author_id, int works_count) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE author set works_count = ? WHERE author_id=?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			
			ps.setInt(1, works_count);
			ps.setInt(2,author_id);
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int updateAuthorBio(Integer author_id, String bio) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE author set bio = ? WHERE author_id=?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, bio);
			ps.setInt(2,author_id);
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int updatePenName(Integer author_id, String pen_name) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE author set pen_name = ? WHERE author_id=?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, pen_name);
			ps.setInt(2,author_id);
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int updateAuthorStatus(Integer author_id, Integer status) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE author set status = ? WHERE author_id=?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, status);
			ps.setInt(2,author_id);
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public List<Author> AllAuthor() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		List<Author> userList = new ArrayList();
		try {
			conn = BaseDao.getCon();
			String sql = "SELECT * FROM author";
			pstmt = conn.prepareStatement(sql);
			Author author = null;
			res = pstmt.executeQuery();

			while (res.next()) {
				author = new Author();
				author.setAuthorId(res.getInt("author_id"));
				author.setUsername(res.getString("username"));
				author.setPassword(res.getString("password"));
				author.setRoleName(res.getString("rolename"));
				author.setPenName(res.getString("pen_name"));
				author.setRealName(res.getString("real_name"));
				author.setGender(res.getString("gender"));
				author.setAvatar(res.getString("avatar"));
				author.setBirthPlace(res.getString("birth_place"));
				author.setBio(res.getString("bio"));
				author.setWorksCount(res.getInt("works_count"));
				author.setStatus(res.getInt("status"));

				userList.add(author);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(conn, pstmt, res);
		}
		return userList;
	}

	@Override
	public int updateAuthor(Author author) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "update author set pen_name=?,birth_place=?,works_count=? where author_id=?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, author.getPenName());
			ps.setString(2, author.getBirthPlace());
			ps.setInt(3, author.getWorksCount());
			ps.setInt(4, author.getAuthorId());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}
}
